package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaOtherSearchDTO;
import com.sunda.spmsoversea.entity.OverseaOtherTask;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 海外仓其他出入库 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-12-27
 */
@Mapper
public interface OverseaOtherTaskMapper extends BaseMapper<OverseaOtherTask> {

    /** 获取其他出入库表头分页列表 */
    @Select("<script>" +
            "SELECT OOT.UUID AS \"uuid\",\n" +
            "       OOT.OPERATE_TITLE AS \"operateTitle\",\n" +
            "       OOT.SPMS_ID AS \"spmsId\",\n" +
            "       OOT.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OOT.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OOT.WERKS AS \"werks\",\n" +
            "       OOT.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OOT.CREATE_DATE AS \"createDate\",\n" +
            "       OOT.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OOT.CREATE_USERID AS \"createUserid\",\n" +
            "       OOT.COST_CENTER AS \"costCenter\",\n" +
            "       OOT.REMARKS AS \"remarks\",\n" +
            "       OOT.COMMENTS AS \"comments\",\n" +
            "       OOT.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "       OOT.PURCHASE_ORG AS \"purchaseOrg\",\n" +
            "       OOT.POSTING_DATE AS \"postingDate\",\n" +
            "       OOT.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OOT.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OOT.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OOT.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OOT.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OOT.DATA_VERSION AS \"dataVersion\",\n" +
            "       OOT.CREATE_TIME AS \"createTime\",\n" +
            "       OOT.UPDATE_TIME AS \"updateTime\",\n" +
            "       U.LAST_NAME AS \"username\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "    LEFT JOIN SPMS_USER U ON OOT.CREATE_USERID = U.WORK_CODE\n" +
            "WHERE OOT.SPMS_STATUS != '0'\n" +
            "  <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND OOT.SPMS_ID = #{dto.spmsId}</when>" +
            "  <when test='dto.movementType!=null and dto.movementType != \"\"'> AND OOT.MOVEMENT_TYPE = #{dto.movementType}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OOT.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND OOT.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OOT.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.createDateBegin!=null and dto.createDateBegin != \"\"'> AND TO_DATE(OOT.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.createDateBegin}, 'yyyy-MM-dd') </when>" +
            "  <when test='dto.createDateEnd!=null and dto.createDateEnd != \"\"'> AND TO_DATE(OOT.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.createDateEnd}, 'yyyy-MM-dd') </when>" +
            "ORDER BY OOT.SPMS_STATUS ASC, OOT.UPDATE_TIME DESC" +
            "</script>")
    List<Map<String, Object>> getOtherTaskPage(IPage page, @Param("dto")OverseaOtherSearchDTO dto);

    /** 获取其他出入库表头详细信息 */
    @Select("SELECT OOT.UUID AS \"uuid\",\n" +
            "       OOT.OPERATE_TITLE AS \"operateTitle\",\n" +
            "       OOT.SPMS_ID AS \"spmsId\",\n" +
            "       OOT.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OOT.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OOT.WERKS AS \"werks\",\n" +
            "       OOT.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OOT.CREATE_DATE AS \"createDate\",\n" +
            "       OOT.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OOT.CREATE_USERID AS \"createUserid\",\n" +
            "       OOT.COST_CENTER AS \"costCenter\",\n" +
            "       OOT.REMARKS AS \"remarks\",\n" +
            "       OOT.COMMENTS AS \"comments\",\n" +
            "       OOT.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "       OOT.PURCHASE_ORG AS \"purchaseOrg\",\n" +
            "       OOT.POSTING_DATE AS \"postingDate\",\n" +
            "       OOT.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OOT.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OOT.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OOT.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OOT.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OOT.DATA_VERSION AS \"dataVersion\",\n" +
            "       OOT.CREATE_TIME AS \"createTime\",\n" +
            "       OOT.UPDATE_TIME AS \"updateTime\",\n" +
            "       U.LAST_NAME AS \"username\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "    LEFT JOIN SPMS_USER U ON OOT.CREATE_USERID = U.WORK_CODE \n" +
            "WHERE OOT.UUID = #{uuid} ")
    Map<String, Object> getOtherTaskInfo(@Param("uuid") String uuid);

    @Select("SELECT MAX(TO_NUMBER(OOD.ITEM)) FROM OVERSEA_OTHER_DTL OOD WHERE OOD.UUID = #{uuid} ")
    Integer getOtherTaskMaxItemNumber(@Param("uuid") String uuid);

    /** 报废出库551;零价格出库Z55;零价格入库Z56;收货多收Z16;串货调整309;不可抗力出库Z13;IT专用出库Z47;期初库存增加561;期初库存减少562; 提交SAP表头信息结构 */
    @Select("SELECT OOT.WERKS AS \"werks\",\n" +
            "       OOT.WHS_LOCATION_CODE AS \"lgort\",\n" +
            "       REPLACE(OOT.CREATE_DATE, '-', '') AS \"bldat\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"budat\",\n" +
            "       OOT.MOVEMENT_TYPE AS \"bwart\",\n" +
            "       OOT.SPMS_ID ||' '|| OOT.REMARKS AS \"bktxt\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "WHERE OOT.UUID = #{uuid} ")
    Map<String, Object> otherTaskHead(@Param("uuid") String uuid, @Param("postingDate") String postingDate);

    /** 报废出库551;零价格出库Z55;零价格入库Z56;收货多收Z16;串货调整309;不可抗力出库Z13;IT专用出库Z47;期初库存增加561;期初库存减少562; 提交SAP行项目信息结构 */
    /** 行项目请求SAP结构，增加两个字段 EXBWR 本币金额，AUFNR内部订单号 */
    @Select("SELECT OOD.ITEM AS \"zeile\",\n" +
            "       OOD.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(OOD.ACTUAL_QTY_BASIC_UNIT) AS \"menge\",\n" +
            "       OOD.BASIC_UNIT AS \"meins\",\n" +
            "       OOD.SUPPLIER_NO AS \"lifnr\",\n" +
            "       #{costCenter} AS \"kostl\",\n" +
            "       OOD.WERKS AS \"werks\",\n" +
            "       OOD.WHS_LOCATION_CODE AS \"lgort\",\n" +
            "       OOD.REMARKS AS \"sgtxt\",\n" +
            "       OOD.MATERIAL_NO_ADJUSTED AS \"zshwl\",\n" +
            "       OOD.INTERNAL_ORDER_NUMBER AS \"aufnr\",\n" +
            "       TO_CHAR(OOD.LOCAL_CURRENCY) AS \"exbwr\"\n" +
            "FROM OVERSEA_OTHER_DTL OOD\n" +
            "    LEFT JOIN OVERSEA_OTHER_TASK OOT ON OOD.UUID = OOT.UUID\n" +
            "WHERE OOD.UUID = #{uuid} \n" +
            "  AND OOD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(OOD.ITEM) ")
    List<Map<String, Object>> otherDtlItem(@Param("uuid") String uuid, @Param("costCenter") String costCenter);

    /** 其他出入库从SAP撤回凭证 */
    @Select("SELECT OOT.SAP_VOUCHER_NUMBER AS \"iMblnr\",\n" +
            "       OOT.SAP_VOUCHER_YEAR AS \"iMjahr\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"iBudat\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "WHERE OOT.UUID = #{uuid} ")
    Map<String, Object> dataHeadCancel(@Param("uuid") String uuid, @Param("cancelPostingDate") String cancelPostingDate);

}
